<?php
/**
 * 添加company_id列到categories、sources和paymethods表
 */

// 检查常量是否已定义
if (!defined("Site_Name")) define("Site_Name","Project-D");
if (!defined("Site_Url")) define("Site_Url","http://127.0.0.2");
if (!defined("Site_Lang")) define("Site_Lang","utf8");
if (!defined("Site_Db")) define("Site_Db","localhost");
if (!defined("Site_Dbuser")) define("Site_Dbuser","ydd");
if (!defined("Site_Dbpass")) define("Site_Dbpass","aBs22t7d2KAZiajt");
if (!defined("Site_Dbname")) define("Site_Dbname","ydd");

// 尝试连接数据库
try {
    $conn = new PDO(
        'mysql:host='.Site_Db.';dbname='.Site_Dbname,
        Site_Dbuser, 
        Site_Dbpass,
        array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8")
    );
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    
    echo "数据库连接成功\n";
    
    // 要添加company_id列的表列表
    $tables = array('categories', 'sources', 'paymethods');
    
    foreach ($tables as $table) {
        echo "正在处理表: $table\n";
        
        // 检查company_id列是否已存在
        $stmt = $conn->prepare("SHOW COLUMNS FROM $table LIKE 'company_id'");
        $stmt->execute();
        $columnExists = $stmt->fetchColumn();
        
        if (!$columnExists) {
            // 添加company_id列，外键关联到companies表的id列
            $sql = "ALTER TABLE $table 
                    ADD COLUMN company_id INT(11) NULL DEFAULT NULL, 
                    ADD INDEX idx_company_id (company_id),
                    ADD CONSTRAINT fk_{$table}_company_id 
                    FOREIGN KEY (company_id) 
                    REFERENCES companies(id) 
                    ON DELETE SET NULL 
                    ON UPDATE CASCADE";
            
            $conn->exec($sql);
            echo "$table 表的company_id列添加成功\n";
        } else {
            echo "$table 表的company_id列已经存在\n";
        }
    }
    
} catch (PDOException $e) {
    die("数据库操作失败: " . $e->getMessage());
}